import pymysql
from datetime import datetime, timedelta
import logging
from python_depend.mysql_pool import MySQLPool
from python_depend.time_urtils import date_noon_timestamp, get_current_date, date_to_milliseconds_timestamp


def get_new_records(last_sync_date):
    """从源表获取需要同步的新记录"""
    sql = "SELECT * FROM fear_and_greed WHERE end_date >= %s ORDER BY end_date DESC"
    try:
        connection = webcrawler_mysql_pool.connection()
        with connection.cursor() as cursor:
            cursor.execute(sql, last_sync_date)
            return cursor.fetchall()
    except Exception as e:
        logging.error(f"获取新记录失败: {str(e)}")
        return []

def get_last_date():
    """从源表获取需要同步的新记录"""
    sql = "SELECT max(date) as last_date FROM fear_and_greed limit 1"
    try:
        connection = ainvestdb_mysql_pool.connection()
        with connection.cursor() as cursor:
            cursor.execute(sql)
            return cursor.fetchall()
    except Exception as e:
        logging.error(f"获取新记录失败: {str(e)}")
        return []


def transform_record(record):
    """转换记录格式以适应目标表结构"""
    try:
        return {
            'fear_greed_value': int(record['fear_greed_value']) if record['fear_greed_value'] else None,
            'date': record["end_date"],
            'fear_greed_level': record['value_classification'],
            'create_time': datetime.now(),
            'update_time': datetime.now(),
            'record_time': date_noon_timestamp(record["end_date"], tz='UTC', milliseconds=True)
        }
    except Exception as e:
        logging.warning(f"转换记录失败 (id: {record.get('id')}): {str(e)}")
        return None


def sync_records():
    """同步记录到目标表"""
    try:
        yesterday = get_last_date()
        if not yesterday:
            print("未查到最新同步日期")
            return True
        last_sync_date = yesterday[0]['last_date']
        print("最近同步日期:", last_sync_date)
        new_records = get_new_records(last_sync_date)
        print(f"查询到数据:{str(new_records)}")
        if not new_records:
            return True
        insert_sql = """
                        INSERT INTO fear_and_greed 
                        (fear_greed_value, date, fear_greed_level, create_time, update_time,record_time)
                        VALUES (%s, %s, %s, now(),now(),%s)
                        ON DUPLICATE KEY UPDATE
                        fear_greed_value = VALUES(fear_greed_value),
                        fear_greed_level = VALUES(fear_greed_level),
                        update_time = VALUES(update_time)
                    """
        rows = []
        for record in new_records:
            transformed = transform_record(record)
            rows.append((
                transformed['fear_greed_value'],
                transformed['date'],
                transformed['fear_greed_level'],
                transformed['record_time'],
            ))
        try:
            feature_conn = ainvestdb_mysql_pool.connection()
            with feature_conn.cursor() as cursor:
                cursor.executemany(insert_sql, rows)
                feature_conn.commit()
                print("成功写入数据")
        except Exception as e:
            logging.error(f"获取新记录失败: {str(e)}")
            return []
    except Exception as e:
        logging.error(f"同步过程出错: {str(e)}")
        return False


if __name__ == "__main__":
    MySQLPool.initialize()
    webcrawler_mysql_pool = MySQLPool.get_pool('webcrawler')
    ainvestdb_mysql_pool = MySQLPool.get_pool('ainvestdb')
    sync_records()
    MySQLPool.close_all()
